package com.itmk.student_course.dao.impl;

import com.itmk.common.BaseDao;
import com.itmk.student_course.dao.StudentCourseDao;
import com.itmk.student_course.entity.StudentCourse;
import com.itmk.student_select.entity.StudentSelectParm;
import com.itmk.student_select.entity.StudentSelectVo;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.StringUtils;

import java.sql.SQLException;
import java.util.List;

public class StudentCourseDaoImpl extends BaseDao implements StudentCourseDao {
    @Override
    public void add(StudentCourse studentCourse) {
        //1、定义sql
        String sql = "insert into student_course(course_id,stu_id,teacher_id) values(?,?,?)";
        //2、定义hangdler
        BeanHandler<StudentCourse> handler = new BeanHandler<>(StudentCourse.class);
        //3、定义参数
        Object[] parms = {
             studentCourse.getCourseId(),
             studentCourse.getStuId(),
             studentCourse.getTeacherId()
        };
        try {
            query.insert(sql,handler,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public StudentCourse hasSelectCourse(StudentCourse studentCourse) {
        //1、定义sql
        String sql = "select * from student_course where course_id=? and stu_id=?";
        //2、定义handler
        BeanHandler<StudentCourse> handler = new BeanHandler<>(StudentCourse.class,processor);
        //3、定义参数
        Object[] parms = {
                studentCourse.getCourseId(),
                studentCourse.getStuId()
        };

        try {
            return query.query(sql,handler,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<StudentSelectVo> list(StudentSelectParm parm) {
        //1、定义sql
        String sqls = "SELECT cs.*,tc.teacher_name,mj.major_name from student_course sc left join course as cs on cs.course_id = sc.course_id  left join teacher as tc on tc.teacher_id = sc.teacher_id left join major as mj on mj.major_id = cs.major_id\n" +
                "where 1=1 ";
        StringBuilder sb = new StringBuilder(sqls);
        //处理关键字
        if(StringUtils.isNotEmpty(parm.getCourseName())){
            sb.append(" and cs.course_name like '%" + parm.getCourseName() +"%'");
        }
        //学生的id
        sb.append(" and sc.stu_id =?");
        //分页处理
        sb.append(" limit ?,?");
        String sql = sb.toString();
        //2、定义handler
        BeanListHandler<StudentSelectVo> handler = new BeanListHandler<>(StudentSelectVo.class,processor);
        //定义参数
        Object[] parms = {
                parm.getStuId(),
                (parm.getPage()-1)*parm.getLimit(),
                parm.getLimit()
        };
        try {
            return query.query(sql,handler,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int getCount(StudentSelectParm parm) {
         //1、定义sql
        String sqls = "SELECT count(sc.student_course_id) from student_course sc left join course as cs on cs.course_id = sc.course_id" +
                "  " +
                "left join teacher as tc on tc.teacher_id = sc.teacher_id left join major as mj on mj.major_id = cs.major_id\n" +
                "where 1=1";
        StringBuilder sb = new StringBuilder(sqls);
        //处理关键字
        if(StringUtils.isNotEmpty(parm.getCourseName())){
            sb.append(" and cs.course_name like '%" + parm.getCourseName() +"%'");
        }
        //学生的id
        sb.append(" and sc.stu_id =?");
        String sql = sb.toString();
        //2、定义handler
        ScalarHandler handler = new ScalarHandler();
        //3、参数封装
        try {
           return Integer.valueOf(query.query(sql,handler,parm.getStuId()).toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
}
